﻿using System; 
using System.Collections.Generic;
using System.Text;
using XXX.Models;
using fuzhu;
using System.Data; 
using XXX.Models.XXXEntities; 
using System.Data.SqlClient;
using GLY_XieTong;
using System.Linq;
using SqlSugar;
using XXX.Common;
using System.Xml.Linq;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace XXX.Bo
{
   public static class DBService
    {
        /// <summary>
        /// 如果为空，使用第二个至
        /// </summary>
        /// <param name="str"></param>
        /// <param name="str2"></param>
        /// <returns></returns>
        public static string gnvl(string str,string str2)
        {
            if (string.IsNullOrEmpty(str) || str == "")
            {
                return str2;

            }
            else
                return str;
        }


        public static RetModelbool xietong(string clx, string id)
        {
            try
            {
                string cError = "";
                if (clx=="上海销售订单转采购订单")
                {
                    SqlParameter[] param = new SqlParameter[]{
                                         new SqlParameter("@id",id),
                                         new SqlParameter("@error",SqlDbType.NVarChar,1000)};
                    param[1].Direction = ParameterDirection.Output;
                    DbHelper2.ExecuteNonQuery("zdy_lk_sp_xtpo", param, CommandType.StoredProcedure,CanShu.conStr);
                    cError = param[1].Value.ToString();

                }
               else if (clx == "上海采购订单转亚太销售订单")
                {
                   string  cConStrYatai = DbHelper.GetDbString(DbHelper2.ExecuteScalar("select cvalue from zdy_lk_para where lx=105 ",CanShu.conStr));

                    SqlParameter[] param = new SqlParameter[]{
                                         new SqlParameter("@id",id),
                                         new SqlParameter("@error",SqlDbType.NVarChar,1000)};
                    param[1].Direction = ParameterDirection.Output;
                    DbHelper2.ExecuteNonQuery("zdy_lk_sp_xtjxso", param, CommandType.StoredProcedure, cConStrYatai);
                    cError = param[1].Value.ToString();

                }
                else if (clx == "上海采购订单转江西凌富销售订单")
                {
                    string cConStrJXLF = DbHelper.GetDbString(DbHelper2.ExecuteScalar("select cvalue from zdy_lk_para where lx=132 ", CanShu.conStr));
                    WriteLog writeLog = new WriteLog();
                    writeLog.WriteLogs("上海采购订单转江西凌富销售订单"+cConStrJXLF);
                    SqlParameter[] param = new SqlParameter[]{
                                         new SqlParameter("@id",id),
                                         new SqlParameter("@error",SqlDbType.NVarChar,1000)};
                    param[1].Direction = ParameterDirection.Output;
                    DbHelper2.ExecuteNonQuery("zdy_lk_sp_xtjxso", param, CommandType.StoredProcedure, cConStrJXLF);
                    cError = param[1].Value.ToString();

                }
                else if (clx == "江西销售出库转上海采购入库")
                {
                    SqlParameter[] param = new SqlParameter[]{
                                         new SqlParameter("@id",id),
                                         new SqlParameter("@error",SqlDbType.NVarChar,1000)};
                    param[1].Direction = ParameterDirection.Output;
                    DbHelper2.ExecuteNonQuery("zdy_lk_sp_xtrk", param, CommandType.StoredProcedure, CanShu.conStr);


                    cError = param[1].Value.ToString();

                }
                else if (clx == "江西凌富销售出库转上海采购入库")
                {
                    SqlParameter[] param = new SqlParameter[]{
                                         new SqlParameter("@id",id),
                                         new SqlParameter("@error",SqlDbType.NVarChar,1000)};
                    param[1].Direction = ParameterDirection.Output;
                    DbHelper2.ExecuteNonQuery("zdy_lk_sp_xtrk_jxlf", param, CommandType.StoredProcedure, CanShu.conStr);


                    cError = param[1].Value.ToString();

                }
                return ReOKbool(cError);
            }
            catch (Exception ex)
            {
                return ReErrorbool(ex.Message);
            }

        }

        public static RetModel GetExpressInfoInfo(string cSOCode)
        {
            RetModel ret = new RetModel();
            try
            {
                string cError = "";
               
                string sql = @"update d set cDefine10 = replace(replace(d.cDefine10,';','：'),':','')　
from DispatchList d
where CHARINDEX(';', cDefine10) > 0  ";
                DbHelper2.ExecuteNonQuery(sql,CanShu.conStr);

                sql =string.Format(@"select DISTINCT a.cSOCode,b.cmemo,convert(varchar(20),  d.dcreatesystime,20) as ExpressTime,  
case when charindex(':', d.cDefine10)> 1   then substring(d.cDefine10, 1, charindex(':', d.cDefine10) - 1) else d.cDefine10 end as ExpressCourtier,
case when charindex(':', d.cDefine10)> 1   then substring(d.cDefine10, charindex(':', d.cDefine10)+1,len(d.cDefine10)) else null end as ExpressSn 
from so_somain a
inner join so_sodetails b on a.id = b.id 
inner join dispatchlists c on b.iSOsID = c.iSOsID
inner join DispatchList d on c.dlid = d.DLID
where  a.cSOCode='{0}' ", cSOCode);
                DataTable dt = DbHelper2.ExecuteTable(sql, CanShu.conStr);
                if (dt.Rows.Count > 0)
                {
                    List<ExpressInfo> aiSen001s1 = TableListConverter<ExpressInfo>.TableToList2(dt);
                    List<object> aiSen001s = aiSen001s1.ConvertAll(s => (object)s);
                    return DBService.ReOK(aiSen001s);
                }
                else
                {
                    return DBService.ReError("没有发货信息");
                }
                


            }
            catch (Exception ex)
            {
                return ReError(ex.Message);
            }

        }

        /// <summary>
        /// 获取当天的项目编码
        /// </summary>
        /// <param name="cSOCode"></param>
        /// <returns></returns>
        public static RetModel<QC_XMCode> QC_GetXMCode(DateTime ddt)
        {
            RetModel<QC_XMCode> ret = new RetModel<QC_XMCode>();
            try
            {
                string cError = "";

//                string sql = string.Format(@"select a.cNo as XMCode,convert(varchar(20), a.xmrq,23) as LXDate,convert(varchar(20),a.denddate,23) as JXDate 
//from LK_XM_LX a 
//where a.dMakeDate>='{0}' or a.dModifyDate>='{0}'", ddt.ToString("yyyy-MM-dd"));
                SqlSugarClient dbsrc = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = CanShu.conStrLK,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                });
                var ddate = new SugarParameter("@ddate", ddt.ToString("yyyy-MM-dd"));
                //var ageP = new SugarParameter("@age", null, true);//设置为output
               
 
                List<QC_XMCode> lst = dbsrc.Ado.UseStoredProcedure().SqlQuery<QC_XMCode>("ZDY_LK_QC_XM", ddate);
                //var dt = db.Ado.UseStoredProcedure().GetDataTable("sp_school", nameP, ageP);
                return DBService.ReOK<QC_XMCode>(lst);

                //if (dt.Rows.Count > 0)
                //{
                //    List<ExpressInfo> aiSen001s1 = TableListConverter<ExpressInfo>.TableToList2(dt);
                //    List<object> aiSen001s = aiSen001s1.ConvertAll(s => (object)s);
                //    return DBService.ReOK(aiSen001s);
                //}
                //else
                //{
                //    return DBService.ReError("没有发货信息");
                //}

            }
            catch (Exception ex)
            {
                return ReError<QC_XMCode>(ex.Message);
            }

        }


        /// <summary>
        /// 获取当天的项目编码
        /// </summary>
        /// <param name="cSOCode"></param>
        /// <returns></returns>
        public static RetModel<QC_Person> QC_GetPerson(DateTime ddt)
        {
            RetModel<QC_Person> ret = new RetModel<QC_Person>();
            try
            {
                string cError = "";

                //                string sql = string.Format(@"select a.cNo as XMCode,convert(varchar(20), a.xmrq,23) as LXDate,convert(varchar(20),a.denddate,23) as JXDate 
                //from LK_XM_LX a 
                //where a.dMakeDate>='{0}' or a.dModifyDate>='{0}'", ddt.ToString("yyyy-MM-dd"));
                SqlSugarClient dbsrc = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = CanShu.conStrLK,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                });
                var ddate = new SugarParameter("@ddate", ddt.ToString("yyyy-MM-dd"));
                //var ageP = new SugarParameter("@age", null, true);//设置为output
                List<QC_Person> lst = dbsrc.Ado.UseStoredProcedure().SqlQuery<QC_Person>("ZDY_LK_QC_Person", ddate);

                SqlSugarClient dbsrc2 = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = CanShu.conStrLF,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                }); 
                //var ageP = new SugarParameter("@age", null, true);//设置为output
                List<QC_Person> lst2 = dbsrc2.Ado.UseStoredProcedure().SqlQuery<QC_Person>("ZDY_LK_QC_Person", ddate);
                List<QC_Person> lst3= lst.Concat(lst2).ToList();
                //var dt = db.Ado.UseStoredProcedure().GetDataTable("sp_school", nameP, ageP);
                return DBService.ReOK<QC_Person>(lst3);
 

                //if (dt.Rows.Count > 0)
                //{
                //    List<ExpressInfo> aiSen001s1 = TableListConverter<ExpressInfo>.TableToList2(dt);
                //    List<object> aiSen001s = aiSen001s1.ConvertAll(s => (object)s);
                //    return DBService.ReOK(aiSen001s);
                //}
                //else
                //{
                //    return DBService.ReError("没有发货信息");
                //}

            }
            catch (Exception ex)
            {
                return ReError<QC_Person>(ex.Message);
            }

        }

        /// <summary>
        /// 获取当天的项目编码
        /// </summary>
        /// <param name="cSOCode"></param>
        /// <returns></returns>
        public static RetModel<QC_Person> QC_GetPersonByName(string personname)
        {
            RetModel<QC_Person> ret = new RetModel<QC_Person>();
            try
            {
                string cError = "";

                //                string sql = string.Format(@"select a.cNo as XMCode,convert(varchar(20), a.xmrq,23) as LXDate,convert(varchar(20),a.denddate,23) as JXDate 
                //from LK_XM_LX a 
                //where a.dMakeDate>='{0}' or a.dModifyDate>='{0}'", ddt.ToString("yyyy-MM-dd"));
                SqlSugarClient dbsrc = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = CanShu.conStrLK,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                });
                var ddate = new SugarParameter("@name", personname);
                //var ageP = new SugarParameter("@age", null, true);//设置为output
                List<QC_Person> lst = dbsrc.Ado.UseStoredProcedure().SqlQuery<QC_Person>("ZDY_LK_QC_PersonByName", ddate);

                SqlSugarClient dbsrc2 = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = CanShu.conStrLF,
                    DbType = SqlSugar.DbType.SqlServer,
                    IsAutoCloseConnection = true
                });
                //var ageP = new SugarParameter("@age", null, true);//设置为output
                List<QC_Person> lst2 = dbsrc2.Ado.UseStoredProcedure().SqlQuery<QC_Person>("ZDY_LK_QC_PersonByName", ddate);
                List<QC_Person> lst3 = lst.Concat(lst2).ToList();
                //var dt = db.Ado.UseStoredProcedure().GetDataTable("sp_school", nameP, ageP);
                return DBService.ReOK<QC_Person>(lst3);


                //if (dt.Rows.Count > 0)
                //{
                //    List<ExpressInfo> aiSen001s1 = TableListConverter<ExpressInfo>.TableToList2(dt);
                //    List<object> aiSen001s = aiSen001s1.ConvertAll(s => (object)s);
                //    return DBService.ReOK(aiSen001s);
                //}
                //else
                //{
                //    return DBService.ReError("没有发货信息");
                //}

            }
            catch (Exception ex)
            {
                return ReError<QC_Person>(ex.Message);
            }

        }

        //协同用的，成功编码是120
        public static RetModelbool ReOKbool()
        {
            RetModelbool retModel = new RetModelbool();
            retModel.code = 120;
            retModel.data = true;
            return retModel;
        }
        public static RetModelbool ReOKbool(string msg)
        {
            RetModelbool retModel = new RetModelbool();
            retModel.code = 120;
            retModel.message = msg;
            retModel.data = true;
            return retModel;
        }
        public static RetModelbool ReErrorbool(string msg)
        {
            RetModelbool retModel = new RetModelbool();
            retModel.code = 120;
            retModel.message = msg;
            retModel.data = false;
            return retModel;
        }
        //插入销售订单，客户档案，编码200
        public static RetModel ReOK(List<object> reData)
        {
            RetModel retModel = new RetModel();
            retModel.code = 200;
            retModel.message = null;
            retModel.data = reData;
            return retModel;
        }
        public static RetModel ReError(string msg)
        {
            RetModel retModel = new RetModel();
            retModel.code = 300;
            retModel.message = msg;
            retModel.data = null;
            return retModel;
        }
       
        public static RetModel<T> ReOK<T>(List<T> reData)
        {
            RetModel<T> retModel = new RetModel<T>();
            retModel.code = 200;
            retModel.message = null;
            retModel.data = reData;
            return retModel;
        }
        public static RetModel<T> ReError<T>(string msg)
        {
            RetModel<T> retModel = new RetModel<T>();
            retModel.code = 300;
            retModel.message = msg;
            return retModel;
        }

        //不是list的，直接是类
        public static RetModel2 ReOKObject(object reData)
        {
            RetModel2 retModel = new RetModel2();
            retModel.code = 200;
            retModel.message = null;
            retModel.data = reData;
            return retModel;
        }


        //不是list的，直接是类,错误写在错误信息里
        public static RetModel2 ReErrorObject(string reData)
        {
            RetModel2 retModel = new RetModel2();
            retModel.code = 300;
            retModel.message = reData;
            retModel.data = null;
            return retModel;
        }

    }
}
